Products
In-IDE
IDE extension that lets you fix coding issues before they exist!
Discover SonarQube for IDE
SaaS
Setup is effortless and analysis is automatic for most languages
Discover SonarQube Cloud
Self-Hosted
Fast, accurate analysis; enterprise scalability
Discover SonarQube Server

Secrets
ABAP
Ansible
Apex
AzureResourceManager
C
C#
C++
CloudFormation
COBOL
CSS
Dart
Docker
Flex
GitHub Actions
Go
HTML
Java
JavaScript
JSON
JCL
Kotlin
Kubernetes
Objective C
PHP
PL/I
PL/SQL
Python
RPG
Ruby
Rust
Scala
Shell
Swift
Terraform
Text
TypeScript
T-SQL
VB.NET
VB6
XML
YAML

PL/SQL static code analysis

Unique rules to find Bugs, Vulnerabilities, Security Hotspots, and Code Smells in your PL/SQL code

Tags

Impact

Clean code attribute

GOTO should not be used to jump backwards
Code Smell
"EXIT" should not be used in loops
Code Smell
"GOTO" statements should not be used
Code Smell
Features deprecated in Oracle 18 should not be used
Code Smell
Identifiers should be written in lower case
Code Smell
SQL "JOIN" conditions should involve all joined tables
Code Smell
"TO_NUMBER" should be used with a format model
Code Smell
"SELECT" statements used as argument of "EXISTS" statements should be selective
Code Smell
"CREATE_TIMER" should not be used
Code Smell
"TO_DATE" and "TO_TIMESTAMP" should be used with a datetime format model
Code Smell
Features deprecated in Oracle 12 should not be used
Code Smell
Labels should not be reused in inner scopes
Code Smell
"PLS_INTEGER" types should be used
Code Smell
"FUNCTIONS" should not have "OUT" parameters
Code Smell
"LIKE" clauses should not be used without wildcards
Code Smell
Jump statements should not be redundant
Code Smell
"EXCEPTION WHEN ... THEN" clauses should do more than "RAISE"
Code Smell
Weak "REF CURSOR" types should not be used
Code Smell
Variables should be nullable
Code Smell
"VARCHAR2" should be used
Code Smell
Native SQL joins should be used
Code Smell
"FORALL" should be used
Code Smell
"FETCH ... BULK COLLECT INTO" should be used
Code Smell
Column aliases should be defined using "AS"
Code Smell
Reserved words should be written in upper case
Code Smell
Tables should be aliased
Code Smell
Procedures and functions should be encapsulated in packages
Code Smell
Single line comments should start with "--"
Code Smell
"SIMPLE_INTEGER" should be used instead of "PLS_INTEGER"
Code Smell
Queries should not "SELECT" too many columns
Code Smell
"ROWID" and "UROWID" data types should not be used
Code Smell
"RETURN" should not be used from within a loop
Code Smell
Quoted identifiers should not be used
Code Smell
Procedures should have parameters
Code Smell
"EXECUTE IMMEDIATE" should be used instead of DBMS_SQL procedure calls
Code Smell
Parameter "IN" mode should be specified explicitly
Code Smell
An "ORDER BY" direction should be specified explicitly
Code Smell
Oracle's join operator (+) should not be used
Code Smell
"NUMBER" variables should be declared with precision
Code Smell
"cursor%NOTFOUND" should be used instead of "NOT cursor%FOUND"
Code Smell
Nested subqueries should be avoided
Code Smell
"NATURAL JOIN" queries should not be used
Code Smell
Object attributes should comply with a naming convention
Code Smell
Record fields should comply with a naming convention
Code Smell
Cursors should follow a naming convention
Code Smell
Types should follow a naming convention
Code Smell
Cursor parameters should follow a naming convention
Code Smell
Exceptions should follow a naming convention
Code Smell
Lines in a multiline comment should start with "*"
Code Smell
Exceptions should not be ignored
Code Smell
"END" statements of labeled loops should be labeled
Code Smell
In labeled loops "EXIT" should exit the label
Code Smell
"EXIT WHEN" should be used rather than "IF ... THEN EXIT; END IF;"
Code Smell
"FOR" loop end conditions should not be hard-coded
Code Smell
Whitespace and control characters in string literals should be explicit
Code Smell
Nested loops should be labeled
Code Smell
Loop start and end labels should match
Code Smell
Block start and end labels should match
Code Smell
Nested blocks should be labeled
Code Smell
Large item lists should not be used with "IN" clauses
Code Smell
Variables should not be initialized with "NULL"
Code Smell
"GOTO" should not be used within loops
Code Smell
"RESULT_CACHE" should not be used
Code Smell
"FULL OUTER JOINS" should be used with caution
Code Smell
Blocks containing "EXECUTE IMMEDIATE" should trap all exceptions
Code Smell
"CASE" should be used rather than "DECODE"
Code Smell
"DBMS_OUTPUT.PUT_LINE" should not be used
Code Smell
"EXCEPTION_INIT -20,NNN" calls should be centralized
Code Smell
"CROSS JOIN" queries should not be used
Code Smell
"CREATE OR REPLACE" should be used instead of "CREATE"
Code Smell
Columns should be aliased
Code Smell
Block labels should appear on the same lines as "END"
Code Smell
"END" statements of labeled blocks should be labeled
Code Smell
"LOOP ... END LOOP;" constructs should be avoided
Code Smell
Track parsing failures
Code Smell
"WHEN OTHERS" should not be the only exception handler
Code Smell
CASE should be used for sequences of simple tests
Code Smell
Global public variables should not be defined
Code Smell
Boolean checks should not be inverted
Code Smell
Files should not be too complex
Code Smell
Two branches in a conditional structure should not have exactly the same implementation
Code Smell
Unused assignments should be removed
Code Smell
"INSERT" statements should explicitly list the columns to be set
Code Smell
SQL tables should be joined with the "JOIN" keyword
Code Smell
"LIKE" clauses should not start with wildcard characters
Code Smell
Column names should be used in a SQL "ORDER BY" clause
Code Smell
"%TYPE" and "%ROWTYPE" should not be used in package specification
Code Smell
Function and procedure parameters should comply with a naming convention
Code Smell
Procedures and functions should be documented
Code Smell
SQL statements should not join too many tables
Code Smell
Constraint names should comply with a naming convention
Code Smell
A primary key should be specified during table creation
Code Smell
Magic literals should not be used
Code Smell
"UNION" should be used with caution
Code Smell
"GROUP BY" should not be used in SQL "SELECT" statements
Code Smell
Function and procedure names should comply with a naming convention
Code Smell
Functions and procedures should not be too complex
Code Smell
Columns to be read with a "SELECT" statement should be clearly defined
Code Smell
Unused local variables should be removed
Code Smell
"CASE" structures should not have too many "WHEN" clauses
Code Smell
Deprecated LONG and LONG RAW datatypes should no longer be used
Code Smell
Track lack of copyright and license headers
Code Smell
Reserved words should be written in lower case
Code Smell
Track breaches of an XPath rule
Code Smell
Comments should not be located at the end of lines of code
Code Smell
"IF" statements should not be nested too deeply
Code Smell
"CASE" statements should end with "ELSE" clauses
Code Smell
Track uses of "NOSONAR" comments
Code Smell
"IF ... ELSEIF" constructs should end with "ELSE" clauses
Code Smell
Sections of code should not be commented out
Code Smell
Track comments matching a regular expression
Code Smell
Statements should be on separate lines
Code Smell
Package names should comply with a naming convention
Code Smell
String literals should not be duplicated
Code Smell
Unused procedure and function parameters should be removed
Code Smell
Variables should comply with a naming convention
Code Smell
"WHEN" clauses should not have too many lines
Code Smell
Constant names should comply with a naming convention
Code Smell
SQL EXISTS subqueries should not be used
Code Smell
Track uses of "TODO" tags
Code Smell
Track uses of "FIXME" tags
Code Smell
Lines should not end with trailing whitespaces
Code Smell
Return of boolean expressions should not be wrapped into an "if-then-else" statement
Code Smell
Boolean literals should not be redundant
Code Smell
Variables should not be shadowed
Code Smell
Redundant pairs of parentheses should be removed
Code Smell
Comments should not be nested
Code Smell
Magic numbers should not be used
Code Smell
The "RELIES_ON" clause should not be used
Code Smell
Functions and procedures should not have too many parameters
Code Smell
Mergeable "if" statements should be combined
Code Smell
Unused labels should be removed
Code Smell
"DBMS_UTILITY.FORMAT_ERROR_STACK" and "FORMAT_ERROR_BACKTRACE" should be used together
Code Smell
Compound triggers should define at least two triggers
Code Smell
"WHEN OTHERS" clauses should be used for exception handling
Code Smell
Files should not have too many lines of code
Code Smell
Lines should not be too long
Code Smell
Procedures should not contain "RETURN" statements
Code Smell

"TO_NUMBER" should be used with a format model

intentionality - complete

maintainability

Code Smell

Why is this an issue?

The TO_NUMBER function is converting the value of BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of NUMBER datatype.

Without providing the format of the input, TO_NUMBER will consider the provided value is compliant with the default format. Relying on a default configuration is a source of error because it creates a dependency between the code and the configuration of the ORACLE server where this code is deployed.

The behaviour of the TO_NUMBER function will certainly not be the expected one if the configuration of the ORACLE server is changing.

Noncompliant code example

The following code with return 0 on an ORACLE server running with its default US configuration with p_string = "2,540"

IF ( TO_NUMBER(p_string) >= 0 and TO_NUMBER(p_string) <= TO_NUMBER('50.00') ) THEN
  RETURN 1;
ELSE
  RETURN 0;
END IF;

Compliant solution

The following code with return 1 on an ORACLE server running with its default FR configuration with p_string = "2,540" because the comma will be interpreted as decimal separator instead of thousand separator.

IF ( TO_NUMBER(p_string, '99.99') >= 0 and TO_NUMBER(p_string, '99.99') <= TO_NUMBER('50.00','99.99') ) THEN
  RETURN 1;
ELSE
  RETURN 0;
END IF;

Available In:

Catch issues on the fly,
in your IDE

Detect issues in your GitHub, Azure DevOps Services, Bitbucket Cloud, GitLab repositories

Analyze code in your
on-premise CI

Developer Edition
Available Since
9.1

In-IDE

SaaS

Self-Hosted